#! /bin/bash
if [[ $1 == "" ]];then
   TD_DATE=`date -d '1 days ago' "+%Y-%m-%d"`
else
   TD_DATE=$1
fi
echo '========================================'
echo '==============全量导入==============='
echo '========================================'
/export/server/presto/bin/presto --catalog hive --server hadoop01:8090 --execute "
delete from hive.bxg_dws.dws_signup_daycount;
insert into hive.bxg_dws.dws_signup_daycount
with tmp as (
select
        c."year", -- 年
        c.year_month, -- 月
        c.year_month_day,-- 日,
        c.clue_id,
        r.relationship_id,
        r.itcast_school_id as school_id, -- 校区id
        r.school_name as school_name, -- 校区名称
        c.origin_type, -- 学习方式
        r.itcast_subject_id as subject_id, -- 学科id
        r.name as subject_name, -- 学科名称
        r.origin_channel, -- 来源渠道
        cast(r.tdepart_code as varchar) as department_id, -- 咨询中心id
        r.department_name, -- 咨询中心名称,
        -- 判断条件
        r.payment_state, -- 是否缴费
        c.appeal_status -- 线索是否有效：1-无效，0，2-有效
from
hive.bxg_dwb.dwb_clue_wide c -- 线索宽表
left join hive.bxg_dwb.dwb_relationship_wide r --意向宽表
on r.relationship_id=c.relationship_id)

select

        "year", -- 年
        year_month, -- 月
        year_month_day, -- 日
        case when grouping(school_id)=0 then school_id else null end as school_id, -- 校区id
        case when grouping(school_name)=0 then school_name else null end as school_name, -- 校区名称
        case when grouping(origin_type)=0 then origin_type else null end as  origin_type, -- 学习方式
        case when grouping(subject_id)=0 then  subject_id else null end as subject_id, -- 学科id
        case when grouping(subject_name)=0 then subject_name else null end as subject_name, -- 学科名称
        case when grouping(origin_channel)=0 then origin_channel else null end  as origin_channel, -- 来源渠道
        case when grouping(department_id)=0 then department_id  else null end as department_id, -- 咨询中心id
        case when grouping(department_name)=0 then department_name else null end as  department_name, -- 咨询中心名称
    -- 分组类型
        case
             when grouping("year",year_month,year_month_day,school_id,origin_type,origin_channel,subject_id,department_id) = 15
             then 'school'  -- 日期+校区
             when grouping("year",year_month,year_month_day,school_id,origin_type,origin_channel,subject_id,department_id) = 7
             then 'school+origintype' -- 日期+校区+学习方式
             when grouping("year",year_month,year_month_day,school_id,origin_type,origin_channel,subject_id,department_id) = 21
             then 'subject+origintype'  -- 日期+学科+学习方式
             when grouping("year",year_month,year_month_day,school_id,origin_type,origin_channel,subject_id,department_id) = 5
             then 'school+subject+origintype' -- 日期+校区+学科+学习方式
             when grouping("year",year_month,year_month_day,school_id,origin_type,origin_channel,subject_id,department_id) = 19
             then 'origintype+originchannel'  -- 日期+来源渠道+学习方式
             when  grouping("year",year_month,year_month_day,school_id,origin_type,origin_channel,subject_id,department_id) = 22
             then 'origintype+department' -- 日期+咨询中心+学习方式
             when grouping("year",year_month,year_month_day,school_id,origin_type,origin_channel,subject_id,department_id) = 23
             then 'origintype'  -- 日期+学习方式
             when grouping("year",year_month,year_month_day,school_id,origin_type,origin_channel,subject_id,department_id) = 31
             then 'all'  -- 日期
             else 'others' end as group_type,
        -- 报名人数
        case when grouping(school_id,school_name)=0  -- 日期+校区
             then count(if(school_id is not null and payment_state='PAID',relationship_id,null))
             when grouping(school_id,school_name,origin_type)=0 -- 日期+校区+学习方式
             then count(if(school_id is not null and origin_type is not null and payment_state='PAID',relationship_id,null))
             when grouping(subject_id,subject_name,origin_type)=0  -- 日期+学科+学习方式
             then count(if(subject_id is not null and origin_type is not null and  payment_state='PAID',relationship_id,null))
             when grouping(school_id,school_name,subject_id,subject_name,origin_type)=0 -- 日期+校区+学科+学习方式
             then count(if(school_id is not null and subject_id is not null and origin_type is not null and  payment_state='PAID',relationship_id,null))
             when grouping(origin_type,origin_channel)=0 -- 日期+来源渠道+学习方式
             then count(if(origin_type is not null and origin_channel is not null and  payment_state='PAID',relationship_id,null))
             when grouping(origin_type,department_id)=0 -- 日期+咨询中心+学习方式
             then count(if(origin_type is not null and department_id is not null and  payment_state='PAID',relationship_id,null))
             when grouping(origin_type)=0 -- 日期+学习方式
             then count(if(origin_type is not null and  payment_state='PAID',relationship_id,null))
             when grouping("year",year_month,year_month_day)=0 -- 日期
             then count(if(year_month_day is not null and  payment_state='PAID',relationship_id,null))
             else null end as signup_count,
        --relationship_count,
        case when grouping(school_id,school_name)=0  -- 日期+校区
             then count(if(school_id is not null,relationship_id,null))
             when grouping(school_id,school_name,origin_type)=0 -- 日期+校区+学习方式
             then count(if(school_id is not null and origin_type is not null,relationship_id,null))
             when grouping(subject_id,subject_name,origin_type)=0  -- 日期+学科+学习方式
             then count(if(subject_id is not null and origin_type is not null,relationship_id,null))
             when grouping(school_id,school_name,subject_id,subject_name,origin_type)=0 -- 日期+校区+学科+学习方式
             then count(if(school_id is not null and subject_id is not null and origin_type is not null,relationship_id,null))
             when grouping(origin_type,origin_channel)=0 -- 日期+来源渠道+学习方式
             then count(if(origin_type is not null and origin_channel is not null,relationship_id,null))
             when grouping(origin_type,department_id)=0 -- 日期+咨询中心+学习方式
             then count(if(origin_type is not null and department_id is not null,relationship_id,null))
             when grouping(origin_type)=0 -- 日期+学习方式
             then count(if(origin_type is not null,relationship_id,null))
             when grouping("year",year_month,year_month_day)=0 -- 日期
             then count(if(year_month_day is not null,relationship_id,null))
             else null end as relationship_count,
        --valid_clue_count
        case when grouping(school_id,school_name)=0  -- 日期+校区
             then count(if(school_id is not null and appeal_status=2,clue_id,null))
             when grouping(school_id,school_name,origin_type)=0 -- 日期+校区+学习方式
             then count(if(school_id is not null and origin_type is not null and appeal_status=2,clue_id,null))
             when grouping(subject_id,subject_name,origin_type)=0  -- 日期+学科+学习方式
             then count(if(subject_id is not null and origin_type is not null and appeal_status=2,clue_id,null))
             when grouping(school_id,school_name,subject_id,subject_name,origin_type)=0 -- 日期+校区+学科+学习方式
             then count(if(school_id is not null and subject_id is not null and origin_type is not null and appeal_status=2,clue_id,null))
             when grouping(origin_type,origin_channel)=0 -- 日期+来源渠道+学习方式
             then count(if(origin_type is not null and origin_channel is not null and appeal_status=2,clue_id,null))
             when grouping(origin_type,department_id)=0 -- 日期+咨询中心+学习方式
             then count(if(origin_type is not null and department_id is not null and appeal_status=2,clue_id,null))
             when grouping(origin_type)=0 -- 日期+学习方式
             then count(if(origin_type is not null and appeal_status=2,clue_id,null))
             when grouping("year",year_month,year_month_day)=0 -- 日期
             then count(if(year_month_day is not null and appeal_status=2,clue_id,null))
             else null end as valid_clue_count,
             '${TD_DATE}'  as dt
from
tmp
group by grouping sets(
    ("year",year_month,year_month_day), -- 日期
    ("year",year_month,year_month_day,school_id,school_name), -- 日期+校区
    ("year",year_month,year_month_day,school_id,school_name,origin_type), -- 日期+校区+学习方式
    ("year",year_month,year_month_day,subject_id,subject_name,origin_type), -- 日期+学科+学习方式
    ("year",year_month,year_month_day,school_id,school_name,subject_id,subject_name,origin_type), -- 日期+校区+学科+学习方式
    ("year",year_month,year_month_day,origin_channel,origin_type), -- 日期+来源渠道+学习方式
    ("year",year_month,year_month_day,department_id,department_name,origin_type), -- 日期+咨询中心+学习方式
    ("year",year_month,year_month_day,origin_type) -- 日期+学习方式
    );"